﻿using DIM_Log;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Data.SqlClient;
using System.IO;
using System.Security.Cryptography;
using System.Text;

namespace DIM_Tool
{
    public class Database_sql
    {
        public string IP { get; set; }
        public string Database { get; set; }
        public string User { get; set; }
        public string Pass { get; set; }
        private SqlConnection Conn { get; set; }
        /// <summary>
        /// SQL初始化
        /// </summary>
        /// <param name="str1"></param>
        /// <param name="str2"></param>
        /// <param name="str3"></param>
        /// <param name="str4"></param>
        public Database_sql(string[] str)
        {
            IP = str[0];
            Database = str[1];
            User = str[2];
            Pass = str[3];
        }
        /// <summary>
        /// 数据库连接
        /// </summary>
        /// <returns></returns>
        public Tuple<bool, string> Connect_sql()
        {
            try
            {
                Conn = new SqlConnection
                {
                    ConnectionString = string.Format(@"Data Source = {0};Initial Catalog ={1};User ID = {2};Password = {3};", IP, Database, User, Pass)
                };
                Conn.Open();
                return new Tuple<bool, string>(true, "连接成功");
            }
            catch (Exception ex)
            {
                return new Tuple<bool, string>(false, ex.Message);
            }
        }
        /// <summary>
        /// SQL插入
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public Tuple<bool, string> Insert_sql(string SQL)
        {
            try
            {
                Tuple<bool, string> result = Connect_sql();
                if (result.Item1)
                {
                    SqlCommand cmd = new SqlCommand
                    {
                        Connection = Conn,
                        CommandText = SQL
                    };
                    cmd.ExecuteNonQuery();
                    Conn.Close();
                    return new Tuple<bool, string>(true, "插入成功");
                }
                else
                {
                    return new Tuple<bool, string>(false, result.Item2);
                }
            }
            catch (Exception ex)
            {
                return new Tuple<bool, string>(false, ex.Message);
            }
        }
        /// <summary>
        /// SQL删除
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public Tuple<bool, string> Delete_sql(string SQL)
        {
            try
            {
                Tuple<bool, string> result = Connect_sql();
                if (result.Item1)
                {
                    SqlCommand cmd = new SqlCommand
                    {
                        Connection = Conn,
                        CommandText = SQL
                    };
                    cmd.ExecuteNonQuery();
                    Conn.Close();
                    return new Tuple<bool, string>(true, "删除成功");
                }
                else
                {
                    return new Tuple<bool, string>(false, result.Item2);
                }
            }
            catch (Exception ex)
            {
                return new Tuple<bool, string>(false, ex.Message);
            }
        }
        /// <summary>
        /// SQL更新
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public Tuple<bool, string> Update_sql(string SQL)
        {
            try
            {
                Tuple<bool, string> result = Connect_sql();
                if (result.Item1)
                {
                    SqlCommand cmd = new SqlCommand
                    {
                        Connection = Conn,
                        CommandText = SQL
                    };
                    cmd.ExecuteNonQuery();
                    Conn.Close();
                    return new Tuple<bool, string>(true, "操作成功");
                }
                else
                {
                    return new Tuple<bool, string>(false, result.Item2);
                }
            }
            catch (Exception ex)
            {
                return new Tuple<bool, string>(false, ex.Message);
            }
        }
        /// <summary>
        /// SQL查找
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public Tuple<bool, string> Select_sql(string SQL)
        {
            try
            {
                JObject Body = new JObject();
                Tuple<bool, string> result = Connect_sql();
                if (result.Item1)
                {
                    Program_log.Log_Info("sql连接状态", result.Item2);
                    SqlCommand cmd = new SqlCommand
                    {
                        Connection = Conn,
                        CommandText = SQL
                    };
                    SqlDataReader reader = cmd.ExecuteReader();
                    bool if_ = reader.HasRows;
                    while (reader.Read())
                    {
                        Body.Add("Name", reader.GetString(1));
                        Body.Add("Accunt", reader.GetString(2));
                        Body.Add("Mail", reader.GetString(4));
                        Body.Add("Menony", reader.GetDecimal(5));
                    }
                    Program_log.Log_Info("处理后需要发送的数据", JsonConvert.SerializeObject(Body));
                    Conn.Close();
                    if (if_)
                        return new Tuple<bool, string>(true, JsonConvert.SerializeObject(Body));
                    else
                        return new Tuple<bool, string>(false, "登录失败");
                }
                else
                {
                    return new Tuple<bool, string>(false, result.Item2);
                }
            }
            catch (Exception ex)
            {
                return new Tuple<bool, string>(false, ex.Message);
            }
        }
        public class Return
        {
            public Return(int code, string body, string title)
            {
                this.Code = code;
                this.Data = body;
                this.Msg = title;
            }
            public int Code { get; set; }
            public string Data { get; set; }
            public string Msg { set; get; }
        }
        /// <summary>
        /// 用户操作SQL
        /// </summary>
        public class User_sql
        {
            /// <summary>
            /// 登录方法
            /// </summary>
            /// <param name="Body"></param>
            /// <returns>返回Return类型</returns>
            public Return Login(string Body)
            {
                try
                {
                    JObject TEMP = (JObject)JsonConvert.DeserializeObject(Body);
                    string SQL = string.Format("select * from Table_USERS where ACCOUNT = '{0}' and PASSWORD = '{1}'",
                    TEMP["Accunt"].ToString(), TEMP["Password"].ToString());
                    Tuple<bool, string> Result = Public_Class.Database_Sql.Select_sql(SQL);
                    if (Result.Item1)
                    {
                        return new Return(200, Result.Item2, "登录成功");
                    }
                    else
                    {
                        return new Return(400, Result.Item2, "登录失败");
                    }
                }
                catch (Exception ex)
                {
                    return new Return(404, ex.Message, "登录失败");
                }
            }
            /// <summary>
            /// 注册方法
            /// </summary>
            /// <param name="Body"></param>
            /// <returns>返回Return类型</returns>
            public Return Regedit(string Body)
            {
                try
                {
                    JObject TEMP = (JObject)JsonConvert.DeserializeObject(Body);
                    string SQL = string.Format("insert into Table_USERS(NAME,ACCOUNT,PASSWORD,MAIL)values('{0}','{1}','{2}','{3}');",
                    TEMP["NAME"].ToString(), TEMP["ACCOUNT"].ToString(), TEMP["PASSWORD"].ToString(), TEMP["MAIL"].ToString());
                    Tuple<bool, string> Result = Public_Class.Database_Sql.Insert_sql(SQL);
                    if (Result.Item1)
                    {
                        return new Return(200, Result.Item2, "注册成功");
                    }
                    else
                    {
                        return new Return(400, Result.Item2, "注册失败");
                    }
                }
                catch (Exception ex)
                {
                    return new Return(404, ex.Message, "注册失败");
                }
            }
            /// <summary>
            /// 修改密码方法
            /// </summary>
            /// <param name="Body"></param>
            /// <returns>返回Return类型</returns>
            public Return Change_password(string Body)
            {
                try
                {
                    JObject TEMP = (JObject)JsonConvert.DeserializeObject(Body);
                    string SQL = string.Format("Update Table_USERS set PASSWORD = '{0}' where ACCOUNT = '{1}' and MAIL = '{2}';",
                    TEMP["PASSWORD"].ToString(), TEMP["ACCOUNT"].ToString(), TEMP["MAIL"].ToString());
                    Tuple<bool, string> Result = Public_Class.Database_Sql.Update_sql(SQL);
                    if (Result.Item1)
                    {
                        return new Return(200, Result.Item2, "修改成功");
                    }
                    else
                    {
                        return new Return(400, Result.Item2, "修改失败");
                    }
                }
                catch (Exception ex)
                {
                    return new Return(404, ex.Message, "修改失败");
                }
            }
            /// <summary>
            /// 解密函数
            /// </summary>
            /// <param name="privateKey">私钥</param>
            /// <param name="data">待解密的字符串</param>
            /// <returns></returns>
            public Tuple<bool, string> RSA_Key(string privateKey, string data)
            {
                try
                {
                    RSACryptoServiceProvider rsa = new RSACryptoServiceProvider();
                    rsa.FromXmlString(privateKey);
                    var _data = rsa.Decrypt(Convert.FromBase64String(data.Replace(" ", "+")), false);
                    return new Tuple<bool, string>(true, Encoding.UTF8.GetString(_data));
                }
                catch (Exception ex)
                {
                    return new Tuple<bool, string>(false, ex.Message);
                }
            }
        }
    }
}